package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.gdms.dao.ScoreSelectStudentdao;
import com.zr.gdms.model.Student;
import com.zr.gdms.util.JDBCUtil;

public class ScoreSelectStudentdaoimpl implements ScoreSelectStudentdao {
//根据成绩段查询
	@Override
	public List<Student> getScoreStudents(int sid,int score1,int score2,int page, int rows) {
		// TODO Auto-generated method stub
		List<Student> st=new ArrayList<Student>();
		StringBuffer sq1=new StringBuffer();
		sq1.append(" SELECT student.sid,student.sname,student.sage,student.ssex,student.sdepart,student.stel,student.sstate");
		sq1.append(" FROM student");
		sq1.append(" INNER JOIN score");
		sq1.append(" ON student.sid=score.sid");
		sq1.append(" WHERE score.score BETWEEN ? AND ? limit ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sq1.toString());
			pst.setInt(1, score1);
			pst.setInt(2, score2);			
			pst.setInt(3, (page-1)*rows);//起始位置（页码-1)*条数
			pst.setInt(4, rows);//每页条数
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				Student ss=new Student();
				ss.setSid(rs.getInt("sid"));
				ss.setSname(rs.getString("sname"));
				ss.setSage(rs.getString("sage"));			
				ss.setSsex(rs.getString("ssex"));
				ss.setSdepart(rs.getString("sdepart"));
				ss.setStel(rs.getString("stel"));		
				ss.setSstate(rs.getString("sstate"));			
				st.add(ss);		
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return st;
	}
	
//对成绩表学生数求和
	@Override
	public int getStudentsCount(int sid) {
		// TODO Auto-generated method stub
		int count=0;
		StringBuffer sq1=new StringBuffer();
		sq1.append(" SELECT COUNT(sid) AS scount");
		sq1.append(" from score");	
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sq1.toString());
			ResultSet rt=pst.executeQuery();
			if(rt.next()){
				count=rt.getInt("scount");	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return count;
	}
	

}
